package com.hylanda.util;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

/** 
 * @author zhangy
 * @E-mail:blackoon88@gmail.com 
 * @qq:846579287
 * @version created at：2018年5月25日 上午10:33:52 
 * note
 */
public class PoiUtil {
	private static Logger logger = LoggerFactory.getLogger(PoiUtil.class);
	//当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook
	public static  Workbook getWorkBook(MultipartFile file) {
	        //获得文件名
	        String fileName = file.getOriginalFilename();
	        //创建Workbook工作薄对象，表示整个excel
	        Workbook workbook = null;
	        try {
	            //获取excel文件的io流
	            InputStream is = file.getInputStream();
	            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
	            if(fileName.endsWith("xls")){
	                //2003
	                workbook = new HSSFWorkbook(is);
	            }else if(fileName.endsWith("xlsx")){
	                //2007 及2007以上
	                workbook = new XSSFWorkbook(is);
	                if(file.getSize()>1024*1024*100){
	                	workbook= new SXSSFWorkbook(new XSSFWorkbook(is));
	                }
	            }
	        } catch (IOException e) {
	        	logger.error(e.getMessage());
	        }
	        return workbook;
	    }
		
		 @SuppressWarnings("deprecation")
		public static String getCellValue(Cell cell){
	         String cellValue = "";
	         if(cell == null){
	             return cellValue;
	         }
	     //判断数据的类型
	         switch (cell.getCellType()){
	             case 0: //数字POI 3.15 beta 3. Use CellType.NUMERIC instead.
	                 cellValue = stringDateProcess(cell);
	                 break;
	             case Cell.CELL_TYPE_STRING: //字符串
	                 cellValue = String.valueOf(cell.getStringCellValue());
	                 break;
	             case Cell.CELL_TYPE_BOOLEAN: //Boolean
	                 cellValue = String.valueOf(cell.getBooleanCellValue());
	                 break;
	             case Cell.CELL_TYPE_FORMULA: //公式
	                 cellValue = String.valueOf(cell.getCellFormula());
	                 break;
	             case Cell.CELL_TYPE_BLANK: //空值
	                 cellValue = "";
	                 break;
	             case Cell.CELL_TYPE_ERROR: //故障
	                 cellValue = "非法字符";
	                 break;
	             default:
	                 cellValue = "未知类型";
	                 break;
	         }
	         return cellValue;
	     }
		 /**
	      * 时间格式处理
	      * @return
	      * @author zhangy
	      */
	     public static String stringDateProcess(Cell cell){
	         String result = new String();  
	         if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
	             SimpleDateFormat sdf = null;  
	             if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
	                 sdf = new SimpleDateFormat("HH:mm");  
	             } else {// 日期  
	                 sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");  
	             }  
	             Date date = cell.getDateCellValue();  
	             result = sdf.format(date);  
	         } else if (cell.getCellStyle().getDataFormat() == 58) {  
	             // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
	             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");  
	             double value = cell.getNumericCellValue();  
	             Date date = org.apache.poi.ss.usermodel.DateUtil  
	                     .getJavaDate(value);  
	             result = sdf.format(date);  
	         } else {  
	             double value = cell.getNumericCellValue();  
	             CellStyle style = cell.getCellStyle();  
	             DecimalFormat format = new DecimalFormat();  
	             String temp = style.getDataFormatString();  
	             // 单元格设置成常规  
	             if (temp.equals("General")) {  
	                 format.applyPattern("#.##");  
	             }  
	             result = format.format(value);  
	         }  
	         
	         return result;
	     }
	     
	     public static void main(String[] args) {
	    	 double value=213.123d;
             DecimalFormat format = new DecimalFormat();  
             // 单元格设置成常规  
                 format.applyPattern("#.##");  
            String result = format.format(value);  
            System.out.println(result);
		}
}
